package app.models;

import app.database.MainDatabase;
import framework.temporal.FRDTemporalDBItem;
import framework.temporal.FRDTemporalModel;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.LinkedList;
import oracle.jdbc.OraclePreparedStatement;
import oracle.jdbc.OracleResultSet;
import oracle.sql.STRUCT;

/**
 *
 * @author Lukáš Marek
 */
public class TemporalModel implements FRDTemporalModel
{
	private MainDatabase db;
	private LinkedList<String> columns;					//sloupce v databázi - první definuje identifikátor
	private LinkedList<String> columnNames;			//názvy sloupců v zobrazované tabulce
	private boolean[] canEdit = new boolean [] {false, true, true, true};
	
	public TemporalModel(MainDatabase db)
	{
		this.db = db;
		columns = new LinkedList<>();
		columns.add("object_id");
		columns.add("name");
		columns.add("date_from");
		columns.add("date_until");
		
		columnNames = new LinkedList<>();
		columnNames.add("ID");
		columnNames.add("Jméno");
		columnNames.add("Platnost od");
		columnNames.add("Platnost do");
	}
	
	@Override
	public LinkedList getColumnNames()
	{
		return columnNames;
	}
	
	@Override
	public boolean[] getColumnCanEdit()
	{
		return canEdit;
	}

	@Override
	public LinkedList getData(LinkedList<String> params)
	{
		LinkedList<FRDTemporalDBItem> list = new LinkedList<>();
		//zpracovani parametru filtru
		String where = "";
		if (!params.get(0).equals(""))
		{
			if (!where.equals("")) where += " AND";
			where += " name LIKE '%" + params.get(0) + "%'";
		}
		if (!params.get(1).equals(""))
		{
			if (!where.equals("")) where += " AND";
			where += " NOT ( date_from < " + params.get(1) + " AND date_until < " + params.get(1) + " )";
		}
		if (!params.get(2).equals(""))
		{
			if (!where.equals("")) where += " AND";
			where += "NOT ( date_from > " + params.get(2) + " AND date_until > " + params.get(2) + " )";
		}
		
		String sql = "SELECT * FROM map_objects";
		if (!where.equals("")) sql += " WHERE" + where;
		OracleResultSet rset;
		OraclePreparedStatement stmt;
		try
		{
			stmt = (OraclePreparedStatement) db.conn.prepareStatement(sql);
			rset = (OracleResultSet) stmt.executeQuery();
			while (rset.next())
			{
				LinkedList<String> data = new LinkedList<>();
				FRDTemporalDBItem item = new FRDTemporalDBItem();
				
				item.id = rset.getInt(columns.getFirst());
				for (String column : columns)
				{
					data.add(rset.getString(column));
				}
				item.setData(data);
				list.add(item);
			}
			rset.close();
			stmt.close();
		}
		catch (SQLException ex)
		{
			System.err.println(ex.getMessage());
		}		
		return list;
	}

	@Override
	public int getDataCount(LinkedList<String> params)
	{
		int count = 0;
		String where = "";
		if (!params.get(1).equals(""))
		{
			if (!where.equals("")) where += " AND";
			where += " name LIKE '%" + params.get(1) + "%'";
		}
		if (!params.get(2).equals(""))
		{
			if (!where.equals("")) where += " AND";
			where += " date_from <= " + params.get(2);
		}
		if (!params.get(3).equals(""))
		{
			if (!where.equals("")) where += " AND";
			where += " date_until >= " + params.get(3);
		}
		String sql = "SELECT COUNT(*) FROM map_objects";
		if (!where.equals("")) sql += " WHERE" + where;
		
		OracleResultSet rset;
		OraclePreparedStatement stmt;
		try
		{
			stmt = (OraclePreparedStatement) db.conn.prepareStatement(sql);
			rset = (OracleResultSet) stmt.executeQuery();
			rset.next();
			count = rset.getInt(1);
			rset.close();
			stmt.close();
		}
		catch (SQLException ex)
		{
			System.err.println(ex.getMessage());
		}
		return count;
	}
	
	@Override
	public int updateColumn(int id, int column, String newValue)
	{
		int ret = 0;
		String sql = "UPDATE map_objects SET";
		String where = " WHERE object_id = " + id;
		String set = " " + columns.get(column) + " = '" + newValue + "'";
		try
		{
			ret = db.executeUpdate(sql + set + where);
		}
		catch (SQLException ex)
		{
			System.err.println(ex.getMessage());
		}
		return ret;
	}

	@Override
	public int deleteColumns(int[] selectedRows)
	{
		if (selectedRows.length == 0) return 0;
		String ids = Integer.toString(selectedRows[0]);
		for (int i = 1; i < selectedRows.length; i++)
		{
			ids += ", " + selectedRows[i];
		}
		String sql = "DELETE FROM map_objects WHERE object_id IN (" + ids + ")";
		int ret = 0;
		try
		{
			ret = db.executeUpdate(sql);
		}
		catch (SQLException ex)
		{
			System.err.println(ex.getMessage());
		}
		return ret;
	}

	/**
	 * odstraneni intervalu u zadaneho zaznamu, v pripade nutnosti vytvori i duplicitni zaznam v databazi
	 * @param id
	 * @param from
	 * @param to 
	 */
	public void deleteInterval(int id, int from, int to)
	{
		ResultSet rset;
		int oldFrom, oldTo;
		if (from >= to) return;
		try
		{
			rset = db.executeQuery("SELECT * FROM map_objects WHERE object_id=" + id);
			if(rset.next())
			{
				oldFrom = rset.getInt("date_from");
				oldTo = rset.getInt("date_until");
 
				if (from < oldFrom && to > oldTo)
					db.executeQuery("DELETE FROM map_objects WHERE object_id = " + id); //smazani celeho intervalu
				else if (from < oldFrom && to < oldTo)
					db.executeUpdate("UPDATE map_objects SET date_from=" + to + " WHERE object_id = " + id); //nastavit zacatek na 'to'
				else if (from > oldFrom && to > oldTo)
					db.executeUpdate("UPDATE map_objects SET date_until=" + from + " WHERE object_id = " + id);	//nastavit konec na 'from'
				else if (from > oldFrom && to < oldTo)
				{//rozdelit interval
					//vytvoreni duplikovaneho zaznamu a nastaveni konce intervalu
					STRUCT struct_data = (oracle.sql.STRUCT) rset.getObject("geometry");		//precteni geometrie z databaze
					OraclePreparedStatement stmt;
					stmt = (OraclePreparedStatement) db.conn.prepareStatement("INSERT INTO map_objects (name,type,geometry,date_from,date_until) "
																																	+ "VALUES (?, ?, ?, ?, ?)");
					stmt.setString(1, rset.getString("name"));
					stmt.setInt(2, rset.getInt("type"));
					stmt.setObject(3, struct_data);
					stmt.setInt(4, to);
					stmt.setInt(5, rset.getInt("date_until"));
					stmt.executeUpdate();
					
					//update stavajiciho a nastaveni zacatku intervalu
					db.executeUpdate("UPDATE map_objects SET date_until=" + from + " WHERE object_id = " + id);
				}
			}
		}
		catch (SQLException ex) { System.err.println(ex.getMessage()); }
	}
	
}
